<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class M_schedule extends MY_Model {
	
	private $__table = 'schedule';
	protected $_table = 'schedule';
	protected $_uid = 'id';
	protected $_timestamp = true;
	
	public function get_all_schedule($id=FALSE)
	{
		if($id == FALSE)
		{
			$query = $this->db->get($this->__table);
			return $query->num_rows() >= 1 ? $query->result() : FALSE;
		}else{
			$sql = "SELECT sch.id as schedule_id,
					   sch.time_from,
					   sch.time_to,
					   sch.section_id,
					   sch.subject_id,
					   sch.day,
					   sub.subj_desc,
					   concat(emp.first_name,' ',emp.last_name) as teacher,
					   sch.teacher_id
				FROM schedule sch
				LEFT JOIN subjects sub ON sub.subj_id = sch.subject_id
				LEFT JOIN employees emp ON emp.emp_id = sch.teacher_id
				WHERE sch.id = ?
				ORDER BY sch.time_from ASC
				LIMIT 1";
			$query = $this->db->query($sql,array($id));
			return $query->num_rows() >= 1 ? $query->row() : FALSE;
		}
	}
	
	public function get_all_schedule_by_section($section_id = false)
	{
		$sql = "SELECT sch.id as schedule_id,
					   sch.time_from,
					   sch.time_to,
					   sch.day,
					   sub.subj_desc,
					   concat(emp.first_name,' ',emp.last_name) as teacher,
					   sub.subj_uniq_id,
					   sch.teacher_id as tid
				FROM schedule sch
				LEFT JOIN subjects sub ON sub.subj_id = sch.subject_id
				LEFT JOIN employees emp ON emp.emp_id = sch.teacher_id
				WHERE sch.section_id = ?
				AND sch.is_deleted = 0
				ORDER BY sch.time_from ASC";
		$query = $this->db->query($sql,array($section_id));
		return $query->num_rows() >= 1 ? $query->result() : FALSE;
	}

	/*
	 * Get All Schedules
	 * if schedule does not exist, create record by getting all subjects set in the level curriculum
	 * @return object
	*/
	public function get_all_schedules_from_section_id($section_id = false)
	{
		$schedules = $this->get_all_schedule_by_section($section_id);

		if($schedules){
			return $schedules;
		}

		$this->load->model('M_sections');
		$this->load->model('M_curriculum');

		/* * Get Level by Section * */
		$section = $this->M_sections->get_section($section_id); if(!$section){ return false; }
		$level_id = $section->grade_level_id;

		/* * Get Subject in the Curriculum by Level ID * */
		$subject_curriculum = $this->M_curriculum->get_subjects_available_for_curriculum($level_id);
		// vd($subject_curriculum);
	}
	

	/*
		query to count all grade level for pagnation
		tomex
		11/12/11/2012
	*/	
	public function count_grade_level()
	{
		return $this->db->count_all($this->__table);
	}
	
	/*
		query to fetch all grade level for pagnation
		tomex
		11/12/11/2012
	*/	
	public function fetch_grade_schedule($limit, $start)
	{
        $this->db->limit($limit, $start);
        $query = $this->db->select(array('id','level_desc','level_stat','level_code'))->get($this->__table);
		return $query->num_rows() >= 1 ? $query->result() : FALSE;
	}
	

	function add_schedule($schedule_data)
	{
		$query = $this->db->where($schedule_data)->get($this->__table);
		if($query->num_rows() > 0)
		{
			return array('status'=>'false','msg'=>'Schedule Already Exists');
		}else{
			$this->db->insert($this->__table,$schedule_data);
			if($this->db->affected_rows() > 0)
			{
				return array('status'=>'true','msg'=>'Schedule Was Successfully Added');
			}else{
				return array('status'=>'false','msg'=>'An error Occured while attempting to add Schedule');
			}
		}
		
	}
	
	function update_schedule($schedule_data,$schedule_id)
	{
		// pp($schedule_id);
		// pd($schedule_data);
		$query = $this->db->where($schedule_data)->get($this->__table);
		if($query->num_rows() > 0)
		{
			return array('status'=>'false','msg'=>'Schedule Already Exists');
		}else{
			$schedule_data['updated_at'] = NOW;
			$this->db->where('id',$schedule_id)->set($schedule_data)->update($this->__table);
			if($this->db->affected_rows() > 0)
			{
				return array('status'=>'true','msg'=>'Schedule Was Successfully Updated');
			}else{
				return array('status'=>'false','msg'=>'An error Occured while attempting to Update Schedule');
			}
		}
	}
	
	
	function for_ajax_update_sched($post,$schedule,$section)
	{
		if($post['type'] == 'teacher')
		{
			$this->db->set('teacher_id',$post['option']);
		}
		elseif($post['type'] == 'timefrom')
		{
			$this->db->set('time_from',$post['option']);
		}
		elseif($post['type'] == 'timeto')
		{
			$this->db->set('time_to',$post['option']);
		}
		elseif($post['type'] == 'days')
		{
			$this->db->set('day',$post['option']);
		}else{
		
		}
		
		$this->db->where('id',$schedule)
				 ->where('section_id',$section)
				 ->update('schedule');
		// pd($this->db->last_query());		 
		return $this->db->affected_rows() >= 1 ? TRUE : FALSE;
	}
	
	
	/*
		query to update level_stat to inactive
		tomex
		11/12/11/2012
	*/
	public function destroy($id)
	{
		$data['date_deleted'] = NOW;
		$data['is_deleted'] = 1;
		$this->db->set($data)->where('id',$id)->update($this->__table);
		return $this->db->affected_rows() > 0 ? TRUE : FALSE;
	}
	
	
	/*
		Delete from schedules directly
	
	*/
	public function del_sched($id)
	{
		$this->db->set($data)->where('id',$id)->delete($this->__table);
		return $this->db->affected_rows() > 0 ? TRUE : FALSE;
	}
	
	/*
		query to update level_stat to active
		tomex
		11/12/11/2012
	*/	
	public function undestroy($id)
	{
		$data['date_deleted'] = NOW;
		$data['is_deleted'] = 0;
		$this->db->set($data)->where('id',$id)->update($this->__table);
		return $this->db->affected_rows() > 0 ? TRUE : FALSE;
	}	
	
	/* verify_data
	 * @param array
	 * @param int
	*  verifies if data entered by user is existing already in the database
	*  $strict level 1,2
	*  level 1 = if one field has existing data in database returns true else false
	*  level 2 = if all field has existing data in database returns true else false
	*  12/11/2012
	*/
	public function verify_data($data,$strict_level)
	{		
			if($strict_level == 1)
			{
				$query = $this->db->or_where($data)->get($this->__table);
			}elseif($strict_level == 2){
				$query = $this->db->where($data)->get($this->__table);
			}
			return $query->num_rows() > 0 ? TRUE : FALSE;
	}
	
	
	public function get_schedule($id){
		$q = $this->db->where('schedule_id', $id)->get('schedule');
		return $q->num_rows() >= 1 ? $q->row() : FALSE;
	}
	
	public function get_specific_coloumn($data)
	{	
		$query = $this->db->select($data)->get($this->__table);
		return $query->num_rows() > 0 ? $query->result() : FALSE;
	}
	
	//originial get subjects per section
	public function get_subjects_for_section_and_grade_level($section_id)
	{
		$sql	 = 'SELECT  sub.subj_id,
					sub.subj_code,
					sub.subj_desc
					FROM subjects sub
					LEFT JOIN sections sect ON sect.grade_level_id = sub.grade_level_id
					WHERE sub.subj_id  NOT IN( SELECT subject_id from schedule WHERE section_id = ? ) AND sect.id = ?
					GROUP BY sub.subj_id';
		$query = $this->db->query($sql,array($section_id,$section_id));
		return $query->result();
	}
	
	public function get_schedule_subjects($teacher_id)
	{
		$sql = "SELECT * FROM schedule JOIN subjects ON schedule.subject_id = subjects.subj_id WHERE teacher_id = ?;";
		$query = $this->db->query($sql);
		return $query->num_rows > 0 ? $query->result() : FALSE;
	}
	
	public function get_subject_students($subject_id)
	{
		$sql = "SELECT * FROM schedule
				JOIN enrollments ON section_id = e_section_id
				JOIN profiles ON e_profile_id = profile_id
				WHERE subject_id = ?";
		$query = $this->db->query($sql, array($subject_id));
		return $query->num_rows > 0 ? $query->result() : FALSE;
	}
}
?>
